package com.zhangyx.crawler.comm;

import com.alibaba.druid.pool.DruidDataSource;
import com.zhangyx.crawler.core.SeedManager;
import org.apache.log4j.Logger;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DBUtil {
    public static Logger log = Logger.getLogger(DBUtil.class);

    public static DataSource dataSource = null;
    static {
        init();
    }
    public static void init() {
        System.setProperty("druid.url", PropertiesUtil.get("db_url"));
        System.setProperty("druid.username", PropertiesUtil.get("db_user"));
        System.setProperty("druid.password", PropertiesUtil.get("db_pass"));
        dataSource = new DruidDataSource();
    }

    public static Connection getConn() {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static int executePreparedSQL(String prepSql, Object... data ){
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement statement = conn.prepareStatement(prepSql);
            int count = 0;
            for (Object ob : data) {
                count++;
                if (String.class.isInstance(ob)) {
                    statement.setString(count, (String) ob);
                    continue;
                }
                if (Integer.class.isInstance(ob)) {
                    statement.setInt(count, (Integer) ob);
                    continue;
                }
                if (Boolean.class.isInstance(ob)) {
                    statement.setBoolean(count, (Boolean) ob);
                    continue;
                }
            }
            statement.execute();
            log.info(prepSql+"--> 执行完毕");
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return 1;
    }


    /**
     * 将数据库的键存入缓存
     * 使用方法: user id redis_user_id --->user_id_323131:ok
     * @param table
     * @param tableKey
     * @param redisKeyPrefix
     */
    public static int putKeysToRedis(String table,String tableKey,String redisKeyPrefix) {
        String sql = "select " + tableKey + " from " + table + " limit ?,?";
        int pageStart = 0;
        int pageSize = 1000;
        int id = 100040019;
        Connection conn = null;
        ResultSet resultSet = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement statement = conn.prepareStatement(sql);
            while (true) {
                int count = 0;
                statement.setInt(1, pageStart);
                statement.setInt(2, pageSize);
                resultSet = statement.executeQuery();
                while (resultSet.next()) {
                    Object o = resultSet.getObject(1);
                    count++;
                    SeedManager.addRepeat(redisKeyPrefix + String.valueOf(o));
                }
                //最后一页
                if (count < pageSize) {
                    break;
                }else {
                    log.debug("add 1000 key to redis");
                }
                pageStart += pageSize;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                resultSet.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            return id;
        }
    }
    public static void main(String[] args) throws Exception{
        String sql = "insert into yyy_user(id,user_name,age,addr,week_songs) values(?,?,?,?,?)";
        executePreparedSQL(sql, 1213,"zxc",21,"北京","123,1231");
    }
}
